package com.shuhe.common;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.shuhe.domain.alarm.ColumnInfo;
import com.shuhe.domain.alarm.TableSchema;

import java.util.*;

public class BottomLevelTableGenerator {
    private static final ObjectMapper mapper = new ObjectMapper();

    public static void main(String[] args) throws Exception {
        String[] testJsons = {
                // 扁平结构
                "{\"f1\":\"1\",\"f2\":\"22\",\"f3\":\"333\",\"f4\":\"4444\",\"f5\":\"55555\",\"f6\":\"666666\",\"f7\":\"7777777\",\"f8\":\"88888888\",\"f9\":\"999999999\"}",
                // 嵌套结构
                "{\"status\":\"success\",\"table\":{\"name\": \"name1\", \"data\":[{\"id\":1,\"name\":\"test1\"},{\"id\":2,\"name\":\"test2\"}]}}",
                // 混合结构
                "{\"status\":\"success\",\"table\":{\"name\": \"name1\", \"comment\":\"comment1\",\"data\":[{\"id\":1,\"name\":\"test1\", \"abc\": \"abc1\",\"comment\":\"comment1\"},\n" +
                        "  {\"id\":2,  \"name\":\"test2\",\"comment\":\"comment1\"}]}}",
                // 带下划线的扁平结构
                "{\"a_1\":\"short\",\"b_1\":\"medium_text_here\",\"c_1\":\"this_is_a_very_long_text_value_that_might_be_used\"}"
        };

        for (int i = 0; i < testJsons.length; i++) {
            System.out.println("=== 生成的建表语句 ===" + i);
            List<TableSchema> schemas = generateBottomLevelTables(testJsons[i], "main_table");

            for (TableSchema schema : schemas) {
                System.out.println(schema.toSQL("main_table"));
                System.out.println();
                System.out.println(schema.getJsonNode());
            }
            System.out.println("=== 生成的建表语句 ===" + i);
        }
    }

    public static List<TableSchema> generateBottomLevelTables(String jsonStr, String baseTableName) throws Exception {
        JsonNode rootNode = mapper.readTree(jsonStr);
        List<TableSchema> schemas = new ArrayList<>();
        Set<String> processedTables = new HashSet<>();

        // 使用新的递归处理逻辑
        analyzeJsonNode(null, rootNode, baseTableName, schemas, processedTables, true);

        return schemas;
    }

    private static void analyzeJsonNode(JsonNode arrayNode, JsonNode firstElement, String tableName,
                                        List<TableSchema> schemas, Set<String> processedTables, boolean isRoot) {

        if (firstElement == null || processedTables.contains(tableName)) {
            return;
        }
        processedTables.add(tableName);

        if (firstElement.isObject()) {
            // 检查是否有数组字段
            boolean hasArray = hasArrayField(firstElement);

            if (!hasArray && isBottomLevelObject(firstElement)) {
                // 最底层对象，创建表
                createTableFromObject(arrayNode, firstElement, tableName, schemas);
            } else {
                // 有嵌套结构，递归处理每个字段
                Iterator<Map.Entry<String, JsonNode>> fields = firstElement.fields();
                while (fields.hasNext()) {
                    Map.Entry<String, JsonNode> field = fields.next();
                    String fieldName = field.getKey();
                    JsonNode value = field.getValue();

                    if (value.isObject()) {
                        // 递归处理嵌套对象
                        processedTables.remove(tableName);
                        analyzeJsonNode(arrayNode, value, tableName, schemas, processedTables, false);
                    } else if (value.isArray()) {
                        // 处理数组
                        handleArrayField(value, tableName, fieldName, schemas, processedTables);
                    }
                }

                // 如果是根节点且没有生成任何表，则创建当前层的表
                if (isRoot && schemas.isEmpty()) {
                    createTableFromObject(arrayNode, firstElement, tableName, schemas);
                }
            }
        }
    }

    private static void handleArrayField(JsonNode arrayNode, String parentTable, String fieldName,
                                         List<TableSchema> schemas, Set<String> processedTables) {

        if (arrayNode == null || !arrayNode.isArray() || arrayNode.size() == 0) {
            return;
        }
        // 取第一个元素分析结构
        JsonNode firstElement = arrayNode.get(0);
        if (firstElement.isObject()) {
            String arrayTableName = parentTable + "_" + fieldName;
            analyzeJsonNode(arrayNode, firstElement, arrayTableName, schemas, processedTables, false);
        }
    }

    private static boolean hasArrayField(JsonNode node) {
        if (!node.isObject()) return false;

        Iterator<Map.Entry<String, JsonNode>> fields = node.fields();
        while (fields.hasNext()) {
            Map.Entry<String, JsonNode> field = fields.next();
            if (field.getValue().isArray()) {
                return true;
            }
        }
        return false;
    }

    private static boolean isBottomLevelObject(JsonNode node) {
        if (!node.isObject()) return false;

        Iterator<Map.Entry<String, JsonNode>> fields = node.fields();
        while (fields.hasNext()) {
            Map.Entry<String, JsonNode> field = fields.next();
            JsonNode value = field.getValue();
            if (value.isObject() || value.isArray()) {
                return false;
            }
        }
        return true;
    }

    private static void createTableFromObject(JsonNode arrayNode, JsonNode firstElement, String tableName,
                                              List<TableSchema> schemas) {
        if (firstElement == null || !firstElement.isObject()) {
            return;
        }

        TableSchema table = new TableSchema(tableName);

        // 添加系统字段
        table.addColumn(new ColumnInfo("ods_uuid", "VARCHAR(50) PRIMARY KEY", false));
        table.addColumn(new ColumnInfo("ods_timestamp", "VARCHAR(30)", false));
        table.addColumn(new ColumnInfo("topic", "VARCHAR(60)", false));

        // 添加数据字段
        Iterator<Map.Entry<String, JsonNode>> fields = firstElement.fields();
        while (fields.hasNext()) {
            Map.Entry<String, JsonNode> field = fields.next();
            String fieldName = field.getKey();
            JsonNode value = field.getValue();

            // 只处理基本类型字段，忽略嵌套对象和数组
            if (!value.isObject() && !value.isArray()) {
                String columnName = normalizeColumnName(fieldName);
                int valueLength = value.asText().length();
                int varcharLength = calculateVarcharLength(valueLength);
                String columnType = "VARCHAR(" + varcharLength + ")";
                table.addColumn(new ColumnInfo(columnName, columnType, true));
            }
        }
        if (arrayNode != null) {
            table.setJsonNode(arrayNode);
        } else {
            table.setJsonNode(firstElement);
        }

        schemas.add(table);
    }

    private static int calculateVarcharLength(int valueLength) {
        int calculatedLength = valueLength * 5;

        if (calculatedLength < 10) {
            return 10;
        }

        if (calculatedLength > 4000) {
            return 4000;
        }

        return (int) Math.ceil(calculatedLength / 10.0) * 10;
    }

    public static String normalizeColumnName(String fieldName) {
        return fieldName.replaceAll("([a-z])([A-Z])", "$1_$2")
                .toLowerCase()
                .replaceAll("[^a-z0-9_]", "_");
    }
}
